﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonsLib.Utility;
using System.Data.SqlClient;
using MaxLeaf.Platform.Functions;
using System.Data.Common;
using MaxLeaf.Platform.Commons;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;
using System.Data;

namespace MaxLeaf.Platform.DALImplementation
{
    public class ItemQryDAL : BaseDAL
    {
        public override List<object> SelectByCond(DBAccess dbcon, object model, List<Cond> lstCond, List<SortBy> lstSortBy, List<FieldFilter> lstFieldFilter, ref PageInfo pageInfo)
        {
            string strSql = string.Empty;
            List<object> lst = new List<object>();


            string strTableName = GetTableName(model);
            string strKeyName = DataFunc.GetKeyField(model);
            string strFields = string.Empty;
            bool blnFieldFilter = false;
            if (lstFieldFilter != null && lstFieldFilter.Count > 0)
            {
                blnFieldFilter = true;
                strFields = GetSelectFields(model, lstFieldFilter);
            }
            else
            {
                strFields = DataFunc.GetFields(model);
            }
            if (("," + strFields + ",").IndexOf("," + strKeyName + ",", StringComparison.InvariantCultureIgnoreCase) < 0)
            {
                strFields += "," + strKeyName;
            }
            string strSortBy = GetSortBy(model, lstSortBy);
            string strSortByFields = GetSortByFields(model, lstSortBy);
            if (("," + strSortBy).IndexOf("," + strKeyName + " ", StringComparison.InvariantCultureIgnoreCase) < 0)
            {
                strSortBy += "," + strKeyName + " ASC";
            }
            IDataParameter[] dataParams;
            const int TotalCountCol = 9;
            const int TotalPageCountCol = 10;
            if (pageInfo != null && pageInfo.PageSize > 0)
            {


                SqlParameter paramTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
                paramTotalCount.Direction = ParameterDirection.Output;
                SqlParameter paramTotalPageCount = new SqlParameter("@TotalPageCount", SqlDbType.Int);
                paramTotalPageCount.Direction = ParameterDirection.Output;
                strSql = "exec P_viewPage @TableName,@FieldList,@PrimaryKey,@Where,@Order,@SortType,@RecorderCount,@PageSize,@PageIndex,@TotalCount Output,@TotalPageCount Output ";
                dataParams = new SqlParameter[] { 
                    new SqlParameter("@TableName",strTableName)
                    , new SqlParameter("@FieldList",strKeyName+strSortByFields)
                    , new SqlParameter("@PrimaryKey",strKeyName)
                    , new SqlParameter("@Where",GetCondForPage(dbcon, model, lstCond))
                    , new SqlParameter("@Order",strSortBy)
                    , new SqlParameter("@SortType","3")
                     , new SqlParameter("@RecorderCount","0")
                     , new SqlParameter("@PageSize",pageInfo.PageSize.ToString())
                     , new SqlParameter("@PageIndex",pageInfo.PageIndex.ToString())

                     , paramTotalCount
                     , paramTotalPageCount
                };

                List<string> lstKeys = new List<string>();
                using (DbDataReader rdr = dbcon.GetDataReader(strSql, dataParams))
                {
                    
                    while (rdr.Read())
                    {
                       
                        lstKeys.Add(rdr.GetInt32(0).ToString());
                    }

                }

                if (pageInfo != null && pageInfo.PageSize > 0)
                {
                    pageInfo.TotalCount = CvtFunc.ToInt(dataParams[TotalCountCol].Value);
                    pageInfo.TotalPageCount = CvtFunc.ToInt(dataParams[TotalPageCountCol].Value);
                }

                if (lstKeys.Count > 0)
                {
                    strSql = string.Format("select {2} from {0} where {1} order by {3}"
                      , strTableName
                      , string.Format("ItemId in ({0})",string.Join(",", lstKeys.ToArray() ))
                      , strFields
                      , strSortBy);
                    

                    using (DbDataReader rdr = dbcon.GetDataReader(strSql ))
                    {
                        Type type = model.GetType();
                        while (rdr.Read())
                        {
                            model = System.Activator.CreateInstance(type);
                            DataFunc.Read(model, rdr, blnFieldFilter);
                            lst.Add(model);
                        }

                    }
                }
            }
            else
            {

                KeyValuePair<string, IDataParameter[]> kvp = GetCond(dbcon, model, lstCond);
                strSql = string.Format("select {2} from {0} where {1} order by {3}"
                  , strTableName
                  , kvp.Key
                  , strFields
                  , strSortBy);
                dataParams = kvp.Value;

                using (DbDataReader rdr = dbcon.GetDataReader(strSql, dataParams))
                {
                    Type type = model.GetType();
                    while (rdr.Read())
                    {
                        model = System.Activator.CreateInstance(type);
                        DataFunc.Read(model, rdr, blnFieldFilter);
                        lst.Add(model);
                    }

                }

                if (pageInfo != null && pageInfo.PageSize > 0)
                {
                    pageInfo.TotalCount = CvtFunc.ToInt(dataParams[TotalCountCol].Value);
                    pageInfo.TotalPageCount = CvtFunc.ToInt(dataParams[TotalPageCountCol].Value);
                }
            }


           


            return lst;
        }

        protected override string GetCondForPage(DBAccess dbcon, object model, List<Cond> lstCond)
        {
            string strCond = base.GetCondForPage(dbcon, model, lstCond);
            ItemQryDataModel tModel = (ItemQryDataModel)model;
            if (lstCond != null)
            {
                foreach (Cond cond in lstCond)
                {
                    if (cond.PropName.Equals("Func_ExistInGoodsCategory", StringComparison.InvariantCultureIgnoreCase))
                    {
                        if (!string.IsNullOrEmpty(strCond))
                        {
                            strCond += " and ";
                        }
                        strCond += "  dbo.F_ExistInGoodsCategory(GoodsID," + cond.Value + " )= " + (cond.Compare == CompareType.Equal ? "1" : "0");
                        continue;
                    }
                    else if (cond.PropName.Equals("Func_CanUseUnit", StringComparison.InvariantCultureIgnoreCase))
                    {
                        if (!string.IsNullOrEmpty(strCond))
                        {
                            strCond += " and ";
                        }
                        strCond += string.Format(@"  exists( select goods_measure_unit.measure_unit_id from goods_main inner join goods_unit_group on goods_main.unit_group_id=goods_unit_group.unit_group_id
                                inner join goods_measure_unit on goods_measure_unit.unit_group_id=goods_unit_group.unit_group_id
                                where goods_main.Goods_ID=GoodsID  and goods_measure_unit.measure_unit_id={0}
                            )"
                            ,CvtFunc.ToInt( cond.Value)
                            );
                        continue;
                    }
                    else if (cond.PropName.Equals("Func_ExistInSCPCategory", StringComparison.InvariantCultureIgnoreCase))
                    {
                        if (!string.IsNullOrEmpty(strCond))
                        {
                            strCond += " and ";
                        }
                        strCond += "  dbo.F_ExistInSCPCategory(" + tModel.UI_CompanyID + ",GoodsID," + cond.Value + " )= " + (cond.Compare == CompareType.Equal ? "1" : "0");
                        continue;
                    }
                    else if (cond.PropName.Equals("Func_ExistInInvLoc", StringComparison.InvariantCultureIgnoreCase))
                    {
                        if (!string.IsNullOrEmpty(strCond))
                        {
                            strCond += " and ";
                        }
                        strCond += "" + (CvtFunc.ToBoolean(cond.Value) ? string.Empty : " not ")
                            + string.Format("  exists (select IminvLoc.* from {0}IminvLoc IminvLoc where a.ItemId=IminvLoc.ItemId and IminvLoc.Companyid={1})"
                                , GetInvDatabase(), DataFunc.GetPropValue(model, "UI_CompanyID")
                            );
                        continue;
                    }
                }
            }
            return strCond;
        }
        public override string GetTableName(object model)
        {
            return ImInvMasterDAL.ItemQryTableName(model);
        }

         
    }
}
